SQL Objects

This subtype of the JOBS object defines SQL-specific processing steps to be carried out in a target system. Like all other job objects (JOBS), SQL jobs can run independently or they can be added to a group (JOBG) or to a workflow.

The page contains the SQL-specific execution parameters:

SQL object (SQL)

Connection Section

Field Description
Connection Object

The object that contains the parameters that will be used to connect to the database. Please take into account that the database types of the agent and of the Connection object must match, otherwise the job will fail.

If you select a connection object here, the server and database parameters defined in it will be used with this job. Therefore, the next two fields (Server:Port and Database) are not available.

When defining the SQL Job object, you can assign it a Login object in the Attributes page. If you do not do so, the login information specified in the Connection object will be used.

Specifying the login information is mandatory; the job will abort if you assign neither a Login nor a Connection object.

When a Connection object of type "Generic JDBC" is used in an SQLjob with a database agent that is configured as "GENERIC", the first usage of the agent defines the database vendor to which the agent is assigned to. Once assigned, the agent cannot be used for other JDBC vendors until it is restarted.
Server:Port

This field is only available if you have not selected an entry in Connection Object.

Name of the computer on which the database has been installed. Enter the name of the server and the port number following this format:

Server name:Port

The standard ports are:

  • MS SQL Server: 1433

  • Oracle: 1521

    It is not necessary to specify a server for jobs that will be processed in an Oracle database in RAC; the Oracle tnsnames.ora file already contains the relevant connection data.

  • DB2: 50000
  • MySQL: 3306

For Informix it is required to specify the port.
Database

This field is only available if you have not selected an entry in Connection Object

Name of the database.

For Informix, the name of the server and of the database must be separated by a slash.

For example: ol_ids_1110_1/ang

Job Report Section

The following table describes the available options:

Field Description
Store to
  • Database: As soon as the job has been processed, the process log available on the target system is stored in the database.
  • File: The process log is stored as a file on the target system.

You can select one option or both of them simultaneously.
Generate

Define when the operating system process log is written.

  • Always: The process log is always written.
  • On error only: The process log is written only when an error occurs, for example, when the job is canceled or aborted.
Add Agent Log Activate this checkbox if you want also the agent messages on the execution of the job to be also included in the task.

Result Table Settings

The results of job SQL executions are written to user-friendly reports. In this section you define how you want those reports to look like:

Field Description
Show

Select the report display options:

Activate the Headlines in a table checkbox if you want the reports to show the headlines

Activate the NULL for empty strings checkbox if you want the reports to display NULL whenever an empty string is returned.
Remove Line Breaks

Activate this option to remove line breaks in columns.
Column Separator

Specify the character that should be used to separate columns in the results of SELECT statements
Substitute Character Specify an alternative character. If the character specified as column separator is used within a database field, the substitute character specified here is used.
Maximum Table Rows

This limitation applies to each SQL statement. If the result of an SQL statement contains more lines than the ones specified here, they are truncated.

The number of lines is not limited if "0" is specified here.
Maximum characters in column

The content of a database field is truncated if it contains more characters than those specified here.

Database fields whose contents were specified as numbers or timestamps are not truncated.

Please take into account that there are limitations to the PREP_PROCESS_REPORT script function when the report is read column by column.

The list below tries to depict a possible road-map to define and work with Job objects and provides short descriptions of the actions you can take, additional information that can help you understand how they work and links to topics that further describe them:

  1. Define the job general settings, which include the following:

    • The basics, on the General Page.
    • If required, apply a Sync object to the job; You do so on the Sync Page.
    • On the Runtime Page specify the job runtime settings.
    • Fine-tune access rights at object level on the Authorization Page.
    • Specify the object Attributes on the Attributes Pages of Executable Objects.
    • Register the output files that will be produced when processing them on the Output Page.
    • To be able to carry out searches in those output files and, if required, perform follow-up actions, specify these settings on the Output-Scan Page.
    • You may want to use variables or prompts. You do so on the Variables & Prompts Pages.
    • You may want to define the settings to backup and restore a job task when included in a workflow. This is useful to recover the last successful status in case of failed processes. You do this on the Rollback Page.
    • The Version Management Page lists all the versions of an object and allows you to restore it to an older version in case of a misconfiguration.
    • Enter information on the job you are defining on the Documentation Page.

  2. Job objects (JOBS) have three pages on which you enter the scripts to be processed. They provide a number of convenience functions to help you with your work. If you enter scripts on all of them, they are processed in the following order:

    1. Pre-Process Page
    2. Process Pages
    3. Post-Process Page
    4. Child Post-Process (SAP and PeopleSoft only), see Child Post-Process Page.
  3. You can easily reuse code using Include Object (JOBI), which saves time and helps you keep your scripts consistent.

  4. Execute the job.

    There are multiple ways to do this that can be grouped as follows:

    • By a parent task.

      This is the case of jobs that are included in a parent object (for example a Workflow or a Group). When defining them, take into account that their activation time can be different from their start time; the latter usually depends on the parent object.

      See Superordinate Tasks (Parents).

    • Stand alone

      This is the case when the job is not part of a parent object or, even if it is, you execute it independently of its parent. You have three possibilities:

    When executable objects are processed, they go through the following four stages: 1. Activation, 2. Generation, 3. Processing and 4. Completion. Take a look at these topics to understand what happens with every processing stage.

  5. When processing jobs, the Automation Engine generates output files and reports that guarantee traceability and auditability. Have a look at the following topics to learn more about this:

  6. In the Process Monitoring perspective a number of functions are available, depending on the status of the job. See Working with Tasks.